package com.feeyo.groundservice.base.rdb;

import io.vertx.core.AbstractVerticle;
import io.vertx.core.Future;
import io.vertx.core.Handler;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.asyncsql.AsyncSQLClient;
import io.vertx.ext.asyncsql.MySQLClient;
import io.vertx.ext.asyncsql.PostgreSQLClient;

import java.util.Arrays;
import java.util.Map;
import java.util.Objects;

import com.feeyo.groundservice.base.util.PropertiesUtils;

/**
 * Created by fooevr on 2016/12/6.
 */
public class PostgresDb extends AbstractVerticle{
    public static AsyncSQLClient postgreSQLClient = null;

    @Override
    public void start(Future<Void> startFuture) throws Exception {
        JsonObject postgreSQLClientConfig = new JsonObject().put("host", PropertiesUtils.getProperty("postgresql.host"))
                .put("port", Integer.parseInt(PropertiesUtils.getProperty("postgresql.port")))
                .put("username", PropertiesUtils.getProperty("postgresql.user"))
                .put("password",PropertiesUtils.getProperty("postgresql.password"))
                .put("database", PropertiesUtils.getProperty("postgresql.database"))
                .put("maxPoolSize", Integer.parseInt(PropertiesUtils.getProperty("postgresql.maxPoolSize")));
        postgreSQLClient = PostgreSQLClient.createNonShared(vertx, postgreSQLClientConfig);
        startFuture.complete();
    }

    public static void ConvertToJsonObject(JsonObject jo, String key){
        if (jo.containsKey(key) && jo.getValue(key) != null){
            try {
                jo.put(key, new JsonObject(jo.getString(key)));
            }
            catch(Exception ex){
                System.out.println(ex.getMessage());
            }
        }
    }
    public static void ConvertToJsonArray(JsonObject jo, String key){
        if (jo.containsKey(key) && jo.getValue(key) != null){
            try {
                jo.put(key, new JsonArray(jo.getString(key)));
            }
            catch(Exception ex){
                System.out.println(ex.getMessage());
            }
        }
    }

    public static void versionableInsert(String schema, String table, JsonObject content, String key, boolean isChange, Handler<JsonObject> handler){
        PostgresDb.postgreSQLClient.getConnection(getConn->{
            if (getConn.succeeded()){
                StringBuilder sb = new StringBuilder("select distinct on (\""+key+"\") * from \"" + schema + "\".\"" + table
                        + "\" where \""+key+"\"=? order by \""+key+"\", _version desc");
                JsonArray params = new JsonArray();
                params.add(content.getValue(key));
                getConn.result().queryWithParams(sb.toString(), params, result->{
                   if (result.succeeded()){
                       JsonObject data = new JsonObject();
                       content.forEach(e-> data.put(e.getKey(), e.getValue()));

                       // 构建记录变动
                       int _version = 1;
                       JsonObject _changemeta = new JsonObject();
                       int _preversionkey = -1;
                       int valueFieldCount = 0;
                       if(result.result().getNumRows() > 0) {
                           for(Map.Entry<String,Object> e : data){
                               if (e.getKey().startsWith("_")){
                                   continue;
                               }
                               if (result.result().getColumnNames().contains(e.getKey())) {
                                   Object oldValue = result.result().getRows().get(0).getValue(e.getKey());

                                   try {
                                       if (e.getValue() instanceof JsonArray) {
                                           oldValue = new JsonArray(oldValue.toString());
                                       }
                                       if (e.getValue() instanceof JsonObject) {
                                           oldValue = new JsonObject(oldValue.toString());
                                       }
                                   }catch (Exception ex){}

                                   if (!Objects.equals(oldValue, e.getValue())){
                                       JsonObject changeBody = new JsonObject();
                                       changeBody.put("new", e.getValue());
                                       changeBody.put("old", oldValue);
                                       _changemeta.put(e.getKey(), changeBody);
                                       valueFieldCount++;
                                   }
                               } else {
                                   System.out.println("[" + e.getKey() + "] doesn't exists in table flight info.");
                               }
                           }
                           _version = result.result().getRows().get(0).getInteger("_version") + 1;
                           _preversionkey = result.result().getRows().get(0).getInteger("id");
                       }else if(isChange){
                           handler.handle(null);
                           getConn.result().close();
                           return;
                       }
                       StringBuilder sbCmd = new StringBuilder();
                       StringBuilder sbParam = new StringBuilder();
                       JsonArray jaParama = new JsonArray();
                       sbCmd.append("insert into \""+schema+"\".\""+table+"\" (");
                       sbParam.append(") values (");

                       // 将历史最新记录放入待插入值
                       if (isChange) {
                           if (result.result().getNumRows() > 0) {
                               result.result().getColumnNames().forEach(col -> {
                                   if (!col.startsWith("_") && !data.containsKey(col)) {
                                       data.put(col, result.result().getRows().get(0).getValue(col));
                                   }
                               });
                           }
                       }

                       // 构建插入命令
                       for(Map.Entry<String, Object> p : data){
                           if(result.result().getColumnNames().contains(p.getKey())) {
                               Object value = p.getValue();
                               if (value instanceof JsonArray || value instanceof JsonObject) {
                                   value = value.toString();
                               }else if(value instanceof String && ((String)value).length() == 0){
                                   continue;
                               }
                               if (value != null) {
                                   sbCmd.append("\"").append(p.getKey()).append("\",");
                                   sbParam.append("?").append(",");
                                   jaParama.add(value);
                                   if (!isChange) {
                                       valueFieldCount++;
                                   }
                               }
                           }else{
                               System.out.println("[" + p.getKey() + "] doesn't exists in table flight info.");
                           }
                       }
                       if (valueFieldCount==0){
                           System.out.println("no field to update.");
                           handler.handle(null);
                           getConn.result().close();
                           return;
                       }
                       if (_preversionkey >= 0){
                           sbCmd.append("\"_preversionkey\",");
                           sbParam.append("?").append(",");
                           jaParama.add(_preversionkey);
                       }
                       if(_changemeta.fieldNames().size() > 0){
                           sbCmd.append("\"_changemeta\",");
                           sbParam.append("?,");
                           jaParama.add(_changemeta.toString());
                       }
                       {
                           sbCmd.append("\"_version\"");
                           sbParam.append("?");
                           jaParama.add(_version);
                       }
                       String cmd = sbCmd.append(sbParam).append(") returning id").toString();
                       getConn.result().queryWithParams(cmd, jaParama, ur->{
                           if (ur.succeeded()){
                               JsonObject notify = new JsonObject();
                               notify.put("source", new JsonObject(){{
                                   put("schema", schema);
                                   put("table", table);
                                   put("pk", new JsonObject(){{
                                       put("id", ur.result().getRows().get(0).getValue("id"));
                                   }});
                                   put("logicid", new JsonObject(){{
                                       put(key, data.getString(key));
                                   }});
                               }});
                               if(isChange) {
                                   notify.put("changemeta", _changemeta);
                               }
                               handler.handle(notify);
                           }
                           if (ur.failed()){
                               System.out.println("exec insert failed: " + ur.cause().getMessage() + "\r\n" + Arrays.toString(ur.cause().getStackTrace()));
                               handler.handle(null);
                           }
                           getConn.result().close();
                       });
                   }
                   if (result.failed()){
                       handler.handle(null);
                       getConn.result().close();
                   }
                });
            }
            if (getConn.failed()){
                getConn.result().close();
                handler.handle(null);
            }
        });
    }
}
